﻿use housing 
go
/*函数-得到三证家庭数*/
if exists(select * from sysobjects where  name='getZuiDiShouRuZhengCount' and xtype='FN' ) drop function getZuiDiShouRuZhengCount
go
Create function getZuiDiShouRuZhengCount(@yearmonth int,@streetName varchar(16),@docName varchar(16),@familyNum varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	select @count=count(*) from apply where 
		effectdate=case when @isWholeyear=1 then effectdate else  @yearmonth end
		and apply_street LIKE '%'+@streetName+'%'
		and (select count(*) from Applicant where Applicant.app_applyid =apply.apply_id)=
			case 
			when @familyNum=-1 then (select count(*) from Applicant where Applicant.app_applyid =apply.apply_id)
			else @familyNum 
			end 
	and  apply_docname=@docName
	return isnull(@count,0)
end 
----SELECT dbo.getZuiDiShouRuZhengCount(200902,'西门','低收入家庭',-1,0)

go
/*函数-得到其他家庭数*/
if exists(select * from sysobjects where  name='getDocOterCount' and xtype='FN' ) drop function getDocOterCount
go
Create function getDocOterCount(@yearmonth int,@streetName varchar(16),@familyNum varchar(16),@isWholeyear bit)
returns int
as 
begin 
declare @count int
	select @count=count(*) from apply where 
		effectdate=case when @isWholeyear=1 then effectdate else  @yearmonth end
		and apply_street LIKE '%'+@streetName+'%'
		and (select count(*) from Applicant where Applicant.app_applyid =apply.apply_id)=
			case 
			when @familyNum=-1 then (select count(*) from Applicant where Applicant.app_applyid =apply.apply_id)
			else @familyNum 
			end 
	and  apply_docname not in ('低收入家庭','低保证','扶助证','特困证')
	return isnull(@count,0)
end 
----SELECT dbo.getDocOterCount(200901,'西门',-1,0)
go

/*函数-得到最低收入家庭数*/
if exists(select * from sysobjects where  name='getLowestCount' and xtype='FN' ) drop function getLowestCount
go
Create function getLowestCount(@yearmonth int,@streetName varchar(16),@familyNum varchar(16),@isWholeyear bit)
returns int
as 
begin 
declare @count int
	select @count=count(*) from apply where 
		effectdate=case when @isWholeyear=1 then effectdate else  @yearmonth end
		and apply_street LIKE '%'+@streetName+'%'
		and (select count(*) from Applicant where Applicant.app_applyid =apply.apply_id)=
			case 
			when @familyNum=-1 then (select count(*) from Applicant where Applicant.app_applyid =apply.apply_id)
			else @familyNum 
			end 
	and  apply_docname  in ('低保证','扶助证','特困证')
	return isnull(@count,0)
end 
----SELECT dbo.getLowestCount(200901,'西门',-1,0)
go



/*函数-得到低收入庭数*/
if exists(select * from sysobjects where  name='getLowerCount' and xtype='FN' ) drop function getLowerCount
go
Create function getLowerCount(@yearmonth int,@streetName varchar(16),@familyNum varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	select @count=count(*) from apply where 
		effectdate=case when @isWholeyear=1 then effectdate else  @yearmonth end
		and apply_street LIKE '%'+@streetName+'%'
		and (select count(*) from Applicant where Applicant.app_applyid =apply.apply_id)=case 
			when @familyNum=-1 then (select count(*) from Applicant where Applicant.app_applyid =apply.apply_id)
			else @familyNum end 
		and apply_docname='低收入家庭'
	return isnull(@count,0)
end 
----SELECT dbo.getLowerCount(200901,'西门',2,0)
GO


/*函数-得到低收入庭数2人以上*/
if exists(select * from sysobjects where  name='getLowerCountTwoMore' and xtype='FN' ) drop function getLowerCountTwoMore
go
Create function getLowerCountTwoMore(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	select @count=count(*) from apply where 
		effectdate=case when @isWholeyear=1 then effectdate else  @yearmonth end
		and apply_street LIKE '%'+@streetName+'%'
		and (select count(*) from Applicant where Applicant.app_applyid =apply.apply_id)>=2
		and apply_docname='低收入家庭'
	return isnull(@count,0)
end 
----SELECT dbo.getLowerCountTwoMore(200901,'西门',0)
GO



/*函数-得到减免金额*/
if exists(select * from sysobjects where  name='getReduceRecordTotalMoney' and xtype='FN' ) drop function getReduceRecordTotalMoney
go
Create function getReduceRecordTotalMoney(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin 
			select @TotalMoney=Sum(isnull(adj_nowreduce,0)) from 
			(
				select distinct  ReduceRecord.adj_noid,ReduceRecord.EffectDate,ReduceRecord.adj_nowreduce
					from ReduceRecord  inner join apply on ReduceRecord.applyid=apply.apply_id  
					where  ReduceRecord.EffectDate=@yearmonth   and  apply_street=@streetName  and adj_nowreduce>0
			) as mytable
		end 
	else
		begin 
			select @TotalMoney=Sum(isnull(adj_nowreduce,0))  from ReduceRecord   inner join apply on ReduceRecord.applyid=apply.apply_id  
			where  adj_nowmoney<>0 and  apply_street=@streetName  and ReduceRecord.EffectDate<=@yearmonth  and adj_id in 
(select min(adj_id) from ReduceRecord  group by ReduceRecord.adj_noid,ReduceRecord.EffectDate)
		end 
	
	
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getReduceRecordTotalMoney(200901,'白云街道')
GO
/*函数-得到减免户数*/
if exists(select * from sysobjects where  name='getReduceRecordCount' and xtype='FN' ) drop function getReduceRecordCount
go
Create function getReduceRecordCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  ReduceRecord.adj_noid,ReduceRecord.EffectDate  from ReduceRecord inner join apply on ReduceRecord.applyid=apply.apply_id  
			where  ReduceRecord.EffectDate=@yearmonth  and  apply_street=@streetName and [adj_nowreduce]>0 
			) as mytable
		end 
	else
		begin 
			select @count=Count(*)  from 
			(
			 select Distinct apply.apply_noid  from ReduceRecord inner join apply on ReduceRecord.applyid=apply.apply_id  
			 where   apply_street=@streetName   and ReduceRecord.EffectDate<=@yearmonth
			) as t
		end	
	return @count
end 
----SELECT dbo.getReduceRecordCount(200901,'江厦',0)
GO



/*函数-得到减免户数增加*/
if exists(select * from sysobjects where  name='getReduceRecordAddCount' and xtype='FN' ) drop function getReduceRecordAddCount
go
Create function getReduceRecordAddCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [ReduceRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [ReduceRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName and a2.[adj_nowreduce]>0   --上月减免
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth and a1.[adj_nowreduce]>0 
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getReduceRecordAddCount(200901,'江厦',0)
GO

/*函数-得到减免金额增加*/
if exists(select * from sysobjects where  name='getReduceRecordMoneyAdd' and xtype='FN' ) drop function getReduceRecordMoneyAdd
go
Create function getReduceRecordMoneyAdd(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull(adj_nowreduce,0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowreduce from [ReduceRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [ReduceRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  --上月减免
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getReduceRecordMoneyAdd(200902,'江厦',0)
GO


 




/*函数-得到减免金额-调整-减少*/
if exists(select * from sysobjects where  name='getReduceRecordChangeMoneyCut' and xtype='FN' ) drop function getReduceRecordChangeMoneyCut
go
Create function getReduceRecordChangeMoneyCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull(adj_reducechange,0)) from [ReduceRecord]
			inner join apply on  ReduceRecord.applyid=apply.Apply_id 
			where adj_reducechange<0  and  adj_nowreduce>0     and  abs(adj_reducechange)<>adj_nowreduce
			and apply.apply_street like '%'+@streetName+'%' and ReduceRecord.effectdate=
			case when @isWholeyear=1 then ReduceRecord.effectdate else @yearmonth end 
		end 
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getReduceRecordChangeMoneyCut(200907,'西门',0)
GO

/*函数-得到减免金额-调整-增加*/
if exists(select * from sysobjects where  name='getReduceRecordChangeMoneyAdd' and xtype='FN' ) drop function getReduceRecordChangeMoneyAdd
go
Create function getReduceRecordChangeMoneyAdd(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull(adj_reducechange,0)) from [ReduceRecord]
			inner join apply on  ReduceRecord.applyid=apply.Apply_id 
			where adj_reducechange>0  and  adj_nowreduce>0     and  adj_reducechange<>adj_nowreduce
			and apply.apply_street like '%'+@streetName+'%' and ReduceRecord.effectdate=
			case when @isWholeyear=1 then ReduceRecord.effectdate else @yearmonth end 
		end 
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getReduceRecordChangeMoneyAdd(200902,'西门',0)
GO




/*函数-得到减免户数减少*/
if exists(select * from sysobjects where  name='getReduceRecordCountCut' and xtype='FN' ) drop function getReduceRecordCountCut
go
Create function getReduceRecordCountCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [ReduceRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [ReduceRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName and  a2.[adj_nowreduce]>0  --本月减免
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1  and  a1.[adj_nowreduce]>0
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getReduceRecordCountCut(200908,'江厦',0)
GO

/*函数-得到减免金额减少*/
if exists(select * from sysobjects where  name='getReduceRecordMoneyCut' and xtype='FN' ) drop function getReduceRecordMoneyCut
go
Create function getReduceRecordMoneyCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull(adj_nowreduce,0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowreduce from [ReduceRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [ReduceRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName and a2.adj_nowreduce>0 --本月减免
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1 and a1.adj_nowreduce>0
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getReduceRecordMoneyCut(200902,'西门',0)
GO


/*函数-得到减免金额-调整-减少*/
if exists(select * from sysobjects where  name='getReduceRecordChangeMoneyCut' and xtype='FN' ) drop function getReduceRecordChangeMoneyCut
go
Create function getReduceRecordChangeMoneyCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull(adj_reducechange,0)) from [ReduceRecord]
			inner join apply on  ReduceRecord.applyid=apply.Apply_id 
			where adj_reducechange<0  and  adj_nowreduce>0    
			and apply.apply_street like '%'+@streetName+'%' and ReduceRecord.effectdate=
			case when @isWholeyear=1 then ReduceRecord.effectdate else @yearmonth end 
		end 
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getReduceRecordChangeMoneyCut(200907,'西门',0)
GO





















/*函数-得到补贴金额--年*/
if exists(select * from sysobjects where  name='getSubsidyRecordTotalMoney' and xtype='FN' ) drop function getSubsidyRecordTotalMoney
go
Create function getSubsidyRecordTotalMoney(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin 
			select @TotalMoney=Sum(isnull([adj_nowmoney],0)) from 
			(
				select distinct  [SubsidyRecord].adj_noid,[SubsidyRecord].EffectDate,[SubsidyRecord].[adj_nowmoney]
					from [SubsidyRecord]  inner join apply on [SubsidyRecord].applyid=apply.apply_id  
					where  [SubsidyRecord].EffectDate=@yearmonth   and  apply_street=@streetName 
			) as mytable
		end 
	else
		begin 
			select @TotalMoney=Sum(isnull([adj_nowmoney],0))  from [SubsidyRecord]   inner join apply on [SubsidyRecord].applyid=apply.apply_id  
			where  adj_nowmoney<>0 and  apply_street=@streetName   and [SubsidyRecord].EffectDate<=@yearmonth and adj_id in 
(select min(adj_id) from [SubsidyRecord]  group by [SubsidyRecord].adj_noid,[SubsidyRecord].EffectDate)
		end 
	
	
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getSubsidyRecordTotalMoney(200902,'西门',0)
GO

/*函数-得到补贴户数*/
if exists(select * from sysobjects where  name='getSubsidyRecordCount' and xtype='FN' ) drop function getSubsidyRecordCount
go
Create function getSubsidyRecordCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  [SubsidyRecord].adj_noid,[SubsidyRecord].EffectDate  from [SubsidyRecord] inner join apply on [SubsidyRecord].applyid=apply.apply_id  
			where  [SubsidyRecord].EffectDate=@yearmonth  and  apply_street=@streetName   and [SubsidyRecord].[adj_nowmoney]>0
			) as mytable
		end 
	else
		begin 
			select @count=Count(*) from  
			(
				select distinct apply.apply_noid from [SubsidyRecord] inner join apply on [SubsidyRecord].applyid=apply.apply_id  
				where   adj_nowmoney<>0 and  apply_street=@streetName  and [SubsidyRecord].[adj_nowmoney]>0   and [SubsidyRecord].EffectDate<=@yearmonth  and adj_id in 
				(select min(adj_id) from [SubsidyRecord]  group by [SubsidyRecord].adj_noid,[SubsidyRecord].EffectDate)
			) as t
		end	
	return @count
end 
----SELECT dbo.getSubsidyRecordCount(200901,'西门街道')
GO


/*函数-得到补贴户数增加*/
if exists(select * from sysobjects where  name='getSubsidyRecordAddCount' and xtype='FN' ) drop function getSubsidyRecordAddCount
go
Create function getSubsidyRecordAddCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [SubsidyRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [SubsidyRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  and a2.[adj_nowmoney]>0 --上月补贴
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth and a1.[adj_nowmoney]>0
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getSubsidyRecordAddCount(200901,'江厦',0)
GO

/*函数-得到补贴金额增加*/
if exists(select * from sysobjects where  name='getSubsidyRecordMoneyAdd' and xtype='FN' ) drop function getSubsidyRecordMoneyAdd
go
Create function getSubsidyRecordMoneyAdd(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_nowmoney],0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowmoney from [SubsidyRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [SubsidyRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  --上月补贴
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getSubsidyRecordMoneyAdd(200902,'江厦',0)
GO


/*函数-得到补贴金额【调整】增加*/
if exists(select * from sysobjects where  name='getSubsidyRecordChangeMoneyAdd' and xtype='FN' ) drop function getSubsidyRecordChangeMoneyAdd
go
Create function getSubsidyRecordChangeMoneyAdd(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_MoneyChange],0)) from SubsidyRecord 
			inner join apply  on SubsidyRecord.applyid=apply.apply_id 
			where    apply.apply_street like '%'+@streetName+'%'  and   apply.EffectDate=@yearmonth
			and   [adj_MoneyChange]>0 and [adj_MoneyChange]<>adj_nowmoney 
		end 
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getSubsidyRecordChangeMoneyAdd(200902,'西门',0)
GO

/*函数-得到补贴金额【调整】减少*/
if exists(select * from sysobjects where  name='getSubsidyRecordChangeMoneyCut' and xtype='FN' ) drop function getSubsidyRecordChangeMoneyCut
go
Create function getSubsidyRecordChangeMoneyCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_MoneyChange],0)) from SubsidyRecord 
			inner join apply  on SubsidyRecord.applyid=apply.apply_id 
			where    apply.apply_street like '%'+@streetName+'%'  and   apply.EffectDate=@yearmonth
			and   [adj_MoneyChange]<0 and adj_nowmoney>0
		end 
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getSubsidyRecordChangeMoneyCut(200902,'西门',0)
GO






/*函数-得到补贴户数减少*/
if exists(select * from sysobjects where  name='getSubsidyRecordCountCut' and xtype='FN' ) drop function getSubsidyRecordCountCut
go
Create function getSubsidyRecordCountCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [SubsidyRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [SubsidyRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName   and a2.[adj_nowmoney]>0--本月补贴
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1 and a1.[adj_nowmoney]>0
			) as mytable
		end 

	return @count
end 
----SELECT dbo.getSubsidyRecordCountCut(200908,'江厦',0)
GO

/*函数-得到补贴金额减少*/
if exists(select * from sysobjects where  name='getSubsidyRecordMoneyCut' and xtype='FN' ) drop function getSubsidyRecordMoneyCut
go
Create function getSubsidyRecordMoneyCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_nowmoney],0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowmoney from [SubsidyRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [SubsidyRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName  --本月补贴
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getSubsidyRecordMoneyCut(200902,'江厦',0)
GO
/*函数-本月补贴补发户数*/
if exists(select * from sysobjects where  name='getAddSubsidyNumCount' and xtype='FN' ) drop function getAddSubsidyNumCount
go
Create function getAddSubsidyNumCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  EffectDate,apply_noid  from apply
			where  EffectDate=@yearmonth  and  apply_street=@streetName  and isnull(AddSubsidyNum,0)>0 and 
				apply_id in (select SubsidyRecord.applyid from SubsidyRecord where SubsidyRecord.EffectDate=@yearmonth)
			) as mytable
		end 
	else
		begin 
			select @count=Count(*) from apply
			where    apply_street=@streetName  and isnull(AddSubsidyNum,0)>0
		end	
	return @count
end 
----SELECT dbo.getAddSubsidyNumCount(200902,'西门',0)
GO

/*函数-本月补贴补发金额*/
if exists(select * from sysobjects where  name='getAddSubsidyNumSum' and xtype='FN' ) drop function getAddSubsidyNumSum
go
Create function getAddSubsidyNumSum(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @count float
  
	if @isWholeyear=0 
		begin  
			select @count=Sum(AddSubsidyNum) 
			from 
			(
				select isnull(AddSubsidyNum,0) as AddSubsidyNum from apply
			where  EffectDate=@yearmonth  and  apply_street=@streetName  and isnull(AddSubsidyNum,0)>0

				and apply_id in (select SubsidyRecord.applyid from SubsidyRecord where SubsidyRecord.EffectDate=@yearmonth)
			) as mytable
		end 
	else
		begin 
			select @count=Sum(isnull(AddSubsidyNum,0)) from apply
			where    apply_street=@streetName  and isnull(AddSubsidyNum,0)>0 and effectDate<=@yearmonth 
				and   apply_id in (select SubsidyRecord.applyid from SubsidyRecord where SubsidyRecord.EffectDate<=@yearmonth  )
 
		end	
	return isnull(@count,0)
end 
----SELECT dbo.getAddSubsidyNumSum(200902,'西门',1)
GO





/*函数-街道名字得到街道编号*/
if exists(select * from sysobjects where  name='getStreetNumIdByStreetName' and xtype='FN' ) drop function getStreetNumIdByStreetName
go
Create function getStreetNumIdByStreetName(@streetName varchar(16))
returns varchar(3)
as 
begin 
	declare @noid varchar(3)
	 select @noid=[str_noid] from [StreetNumId] where [str_streetname]=@streetName
	return  @noid
end 
----SELECT dbo.getStreetNumIdByStreetName('西门街道')
GO



GO
/*函数-得到实物配租户数*/
if exists(select * from sysobjects where  name='getKindRecordCount' and xtype='FN' ) drop function getKindRecordCount
go
Create function getKindRecordCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  KindRecord.adj_noid  from KindRecord inner join apply on KindRecord.applyid=apply.apply_id  
			where  KindRecord.EffectDate=@yearmonth  and  apply_street=@streetName  and apply.isKind=1
			) as mytable
		end 
	else
		begin 
			select @count=Count(*) 
			from 
			( select  distinct adj_noid from KindRecord inner join apply on KindRecord.applyid=apply.apply_id  
			where   apply_street=@streetName   and KindRecord.EffectDate<=@yearmonth and apply.isKind=1
			) as t
		end	
	return isnull(@count,0)
end 
----SELECT dbo.getKindRecordCount(200904,'西门',2)
GO

GO
/*函数-得到实物配租面积*/
if exists(select * from sysobjects where  name='getKindRecordAreaSum' and xtype='FN' ) drop function getKindRecordAreaSum
go
Create function getKindRecordAreaSum(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin 
			select @TotalMoney=Sum(isnull([adj_nowusearea],0)) from 
			(
				select distinct  [KindRecord].adj_noid,[KindRecord].EffectDate,[KindRecord].[adj_nowusearea]
					from [KindRecord]  inner join apply on [KindRecord].applyid=apply.apply_id  
					where  [KindRecord].EffectDate=@yearmonth   and  apply_street=@streetName  and [KindRecord].[adj_nowusearea]>0

					 and apply.isKind=1
			) as mytable
		end 
	else
		begin 
			select @TotalMoney=Sum(isnull([adj_nowusearea],0))  from [KindRecord]   inner join apply on [KindRecord].applyid=apply.apply_id  
			where  [adj_nowusearea]<>0 and  apply_street=@streetName  and [KindRecord].EffectDate<=@yearmonth  and adj_id in 
(select min(adj_id) from [KindRecord]  group by [KindRecord].adj_noid,[KindRecord].EffectDate)  and apply.isKind=1
		end 
	
	
	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getKindRecordAreaSum(200902,'西门',1)
GO





/*函数-得到实物配租户数增加*/
if exists(select * from sysobjects where  name='getKindRecordAddCount' and xtype='FN' ) drop function getKindRecordAddCount
go
Create function getKindRecordAddCount(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  and a2.[adj_nowusearea]>0 --上月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth and a1.[adj_nowusearea]>0   and b1.iskind=1
			) as mytable
		end 

	return isnull(@count,0)
end 
----SELECT dbo.getKindRecordAddCount(200901,'西门',0)
GO

/*函数-得到实物配租面积增加*/
if exists(select * from sysobjects where  name='getKindRecordMoneyAdd' and xtype='FN' ) drop function getKindRecordMoneyAdd
go
Create function getKindRecordMoneyAdd(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_nowusearea],0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowusearea from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth-1  and  b2.apply_street=@streetName  --上月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth and b1.iskind=1
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getKindRecordMoneyAdd(200902,'西门',0)
GO




/*函数-得到实物配租户数减少*/
if exists(select * from sysobjects where  name='getKindRecordCountCut' and xtype='FN' ) drop function getKindRecordCountCut
go
Create function getKindRecordCountCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns int
as 
begin 
	declare @count int
	if @isWholeyear=0 
		begin  
			select @count=Count(*) 
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate  from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName   and a2.[adj_nowusearea]>0--本月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1 and a1.[adj_nowusearea]>0
			) as mytable
		end 

	return isnull(@count,0)
end 
----SELECT dbo.getKindRecordCountCut(200908,'西门',0)
GO

/*函数-得到实物配租面积减少*/
if exists(select * from sysobjects where  name='getKindRecordMoneyCut' and xtype='FN' ) drop function getKindRecordMoneyCut
go
Create function getKindRecordMoneyCut(@yearmonth int,@streetName varchar(16),@isWholeyear bit)
returns float
as 
begin 
	declare @TotalMoney float
	if @isWholeyear=0 
		begin  
			select @TotalMoney=Sum(isnull([adj_nowusearea],0))
			from 
			(
				select distinct  a1.adj_noid,a1.EffectDate,a1.adj_nowusearea from [KindRecord] as a1   inner join apply as b1 on a1.applyid=b1.apply_id  
				where a1.adj_noid not in 
				(
					select a2.adj_noid from [KindRecord] as a2  inner join apply as b2 on a2.applyid=b2.apply_id  
					where  a2.EffectDate=@yearmonth  and  b2.apply_street=@streetName  --本月实物配租
				)
				and b1.apply_street=@streetName and   a1.EffectDate=@yearmonth-1
			) as mytable
		end 

	return isnull(@TotalMoney,0)
end 
----SELECT dbo.getKindRecordMoneyCut(200902,'西门',0)